package com.gydz.gydzbaseframeworkboot.Common.utils.export;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ImportExcelUtil {

    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws IOException
     */
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum()+1; y < row.getLastCellNum(); y++) {  //+1去掉[序号]列
                    cell = row.getCell(y);
                    if(cell == null){
                        li.add("");
                    }else{
                        li.add(this.getCellValue(cell));
                    }
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    /* 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws IOException
     */
    public  List<List<Object>> getListByExcel1(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    if(cell == null){
                        li.add("");
                    }else{
                        li.add(this.getCellValue(cell));
                    }
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    public  List<List<Object>> getBankListByExcel0(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}
            int initJ = sheet.getFirstRowNum();
            boolean flag = false;
            if(i >= 1){
                for (int j = initJ ; j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if(row==null){continue;}
                    //遍历所有的列
                    List<Object> li = new ArrayList<Object>();
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        if(cell != null){
                            String str = this.getCellValue(cell).toString();
                            if(StringUtils.equalsIgnoreCase(str, "序号")){
                                initJ = j + 1;
                                flag = true;
                                break;
                            }
                        }
                    }
                    if(flag){ break;}
                }
            }
            //遍历当前sheet中的所有行(忽略字段列)
            for (int j = initJ ; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    if(cell == null){
                        li.add("");
                    }else{
                        li.add(this.getCellValue(cell));
                    }
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }
    public  List<List<Object>> getBankListByExcel1(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}
            int initJ = sheet.getFirstRowNum();
            boolean flag = false;
            if(i >= 1){
                for (int j = initJ ; j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if(row==null){continue;}
                    //遍历所有的列
                    List<Object> li = new ArrayList<Object>();
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        if(cell != null){
                            String str = this.getCellValue(cell).toString();
                            if(StringUtils.equalsIgnoreCase(str, "序号")){
                                initJ = j + 1;
                                flag = true;
                                break;
                            }
                        }
                    }
                    if(flag){ break;}
                }
            }
            //遍历当前sheet中的所有行(忽略字段列)
            for (int j = initJ+1 ; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    if(cell == null){
                        li.add("");
                    }else{
                        li.add(this.getCellValue(cell));
                    }
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    public  List<HashMap<String,Object>> getListByExcel(InputStream in,String fileName) throws Exception{
        List<HashMap<String,Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<HashMap<String,Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}
            int initJ = sheet.getFirstRowNum();
            boolean flag = false;
            List<String> titleList = new ArrayList<String>();
            for (int j = initJ ; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null){continue;}
                //遍历所有的列
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    if(cell != null){
                        String str = this.getCellValue(cell).toString();
                        if(StringUtils.equalsIgnoreCase(str, "序号")){
                            initJ = j + 1;
                            flag = true;
                        }
                        if(flag){
                            titleList.add(str);
                        }
                    }
                }
                if(flag){ break;}
            } //System.out.println("titleList:"+titleList);
            int tSize = titleList.size();
            //遍历当前sheet中的所有行
            for (int j = initJ ; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null){continue;}
                //遍历所有的列
                HashMap<String,Object> li = new HashMap<String,Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    String t0 = (y < tSize ? titleList.get(y) : "");
                    String title = StringUtils.isBlank(t0) ? "" : t0;
                    if(cell != null){
                        li.put(title,this.getCellValue(cell));
                    }
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003L.equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     * @throws ParseException
     */
    public  Object getCellValue(Cell cell) throws ParseException{
        Object value = null;
        DecimalFormat df = new DecimalFormat("####################.####################");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
        SimpleDateFormat tdf = new SimpleDateFormat("H:mm:ss");  //日期格式化
        SimpleDateFormat dtdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  //日期格式化
        SimpleDateFormat dtdf2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");  //日期格式化
        SimpleDateFormat dateDf = new SimpleDateFormat("yyyy年M月d日");  //日期格式化
        SimpleDateFormat dateDf2 = new SimpleDateFormat("yyyy年M月");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING: //System.out.println("cellDfs:"+cell.getCellStyle().getDataFormatString());
                if(StringUtils.equals(cell.getRichStringCellValue().getString(), "/") || StringUtils.equals(cell.getRichStringCellValue().getString(), "\\") ){
                    value = "";
                }else{
                    //String value0 = cell.getRichStringCellValue().getString();
                    value = cell.getRichStringCellValue().getString();//value0.replaceAll(String.valueOf((char)10), "").replaceAll(String.valueOf((char)13), "");
                }
                break;
            case Cell.CELL_TYPE_FORMULA://System.out.println("cellDfs:"+cell.getCellStyle().getDataFormatString());
                String formatType = cell.getCellStyle().getDataFormatString();
                if (formatType.indexOf("%") != -1){
                    value = Math.round(cell.getNumericCellValue()*100)+"%";
                }else if (formatType.indexOf("@") != -1){
                    cell.setCellType(CellType.STRING);//System.out.println("String:"+cell.getRichStringCellValue().getString());
                    value = cell.getRichStringCellValue().getString();
                }else{
                    value = cell.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_NUMERIC: //System.out.println("cell.getCellStyle().getDataFormatString()="+cell.getCellStyle().getDataFormatString()+"|cell.getDateCellValue()="+cell.getDateCellValue());
                if("General".equals(cell.getCellStyle().getDataFormatString())){
                    value = df.format(cell.getNumericCellValue());
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                    value = sdf.format(cell.getDateCellValue());
                }else if("h:mm:ss".equals(cell.getCellStyle().getDataFormatString())){
                    value = tdf.format(cell.getDateCellValue());
                }else if("[h]:mm:ss".equals(cell.getCellStyle().getDataFormatString())){
                    value = tdf.format(cell.getDateCellValue());
                }else if(StringUtils.contains(cell.getCellStyle().getDataFormatString(), "yy")
                        && StringUtils.contains(cell.getCellStyle().getDataFormatString(), ":mm")){
                    value = dtdf.format(cell.getDateCellValue());
                }else if(StringUtils.isNotBlank(cell.getCellStyle().getDataFormatString()) && (cell.getCellStyle().getDataFormatString()).contains("yyyy")
                        && !cell.getCellStyle().getDataFormatString().contains(":mm")){
                    value = sdf.format(cell.getDateCellValue());
                }else if("reserved-0x1f".equals(cell.getCellStyle().getDataFormatString())){
                    value = dateDf.format(cell.getDateCellValue());
                }else if("reserved-0x1F".equals(cell.getCellStyle().getDataFormatString())){
                    value = dateDf.format(cell.getDateCellValue());
                }else if("0_ ".equals(cell.getCellStyle().getDataFormatString())){
                    value = df.format(cell.getNumericCellValue());
                }else if("@".equals(cell.getCellStyle().getDataFormatString())){
                    value = df.format(cell.getNumericCellValue());
                }else if(57 == cell.getCellStyle().getDataFormat()){
                    value = dateDf2.format(cell.getDateCellValue());
                } else{
                    System.out.println("cellDfs:"+cell.getCellStyle().getDataFormatString()+"|"+cell.getNumericCellValue());
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }
}
